import base64
import os
import time
import traceback
from time import strftime

import pymysql
import requests
import yaml
from apscheduler.schedulers.blocking import BlockingScheduler
from cryptography.fernet import Fernet
from pymysql.cursors import DictCursor

from wxauto import WeChat

# 定时任务执行间隔 单位：分钟
TIME_DIFF = 20
# 正式上线需要修改这个密码
YML_PATH = "conf/application.yml"
# 临时文件地址
TEMP_PATH = "temp"
# 加密密码
KEY = b'kc7aV67sn-Cmb0_qak5E1lJoDjxRJbdP4c0UL8XGjPc='
# 休眠时间
TIME_SLEEP = 3
FILE_MSG_SLEEP = 3
# 默认群名
DEFAULT_QUNM = "龙龙机器人预警群"
PERSON_DEFAULT_QUNM = "个人信息预警群"
CS_DEFAULT_QUNM = "龙龙科技测试"


# 读取yml 配置文件
def read_yaml(filename: str = YML_PATH):
    if not os.path.exists(filename):
        raise FileNotFoundError(f"File {filename} not found")
    with open(filename, "r", encoding="utf-8") as f:
        config = yaml.safe_load(f.read())
        return config


def create_folder_if_not_exists(folder_path):
    if not os.path.exists(folder_path):
        os.makedirs(folder_path)
        print(f"Folder '{folder_path}' created successfully.")
    else:
        print(f"Folder '{folder_path}' already exists.")


# 对称数据解密
def decrypt_data(data):
    if data is None or data == '':
        return ""
    # 使用秘钥创建一个Fernet对象
    cipher_suite = Fernet(KEY)
    decrypted = cipher_suite.decrypt(data.encode('utf-8')).decode('utf-8')
    return decrypted


# 将图片保存为文件
def save_base64_to_img(base64_data, local_file_path):
    byte_data = base64.b64decode(base64_data)
    with open(local_file_path, 'wb') as f:
        f.write(byte_data)


def do_job(ldxt99_db, ldxt99_db_cursor):
    # 查询机构和群名对照表
    sql = "select * from jg_qunm_ys"
    ldxt99_db_cursor.execute(sql)
    jg_qunm_list = ldxt99_db_cursor.fetchall()

    # 查询所有待发送数据
    sql = "select * from sync_zf_khxxtb where content is not null and content != '' order by FQ_TIME asc limit 100"
    ldxt99_db_cursor.execute(sql)
    zfkhxx_list = ldxt99_db_cursor.fetchall()

    # 发送数据到微信群中
    wx = WeChat()

    # 发送所有数据到微信客户端
    for zfkhxx in zfkhxx_list:
        local_file_path = TEMP_PATH + "\\" + zfkhxx["FIELDNAME"]

        # 存储临时图片文件base 64转本地文件
        save_base64_to_img(zfkhxx["IMG"], local_file_path)

        # 解密需要发送的数据
        content = decrypt_data(zfkhxx["CONTENT"])

        # 查找对应的群名
        who = ""
        for jgqunm in jg_qunm_list:
            if jgqunm["jgh"] == zfkhxx["SITENO"]:
                who = jgqunm["qunm"]
                break

        if who == "":
            print(zfkhxx["SITENO"] + "对应的群名缺失，使用默认群名")
            who = DEFAULT_QUNM

        try:
            wx.SendFiles(local_file_path, who)
            time.sleep(FILE_MSG_SLEEP)
            wx.SendMsg(content, who)
        except Exception as e:
            print("发生错误：", str(e))
            print("堆栈信息如下：")
            print(traceback.format_exc())
            wx.SendFiles(local_file_path, DEFAULT_QUNM)
            time.sleep(FILE_MSG_SLEEP)
            wx.SendMsg(content, DEFAULT_QUNM)
            wx.SendMsg("推送信息发送异常，请检查配置的群名" + who + "是否被更新", DEFAULT_QUNM)

        # 删除本地图片
        os.remove(local_file_path)

        # 删除走访客户信息记录
        sql = "update sync_zf_khxxtb set CONTENT = '', IMG = '' where SEQNO = %s"
        ldxt99_db_cursor.execute(sql, zfkhxx["SEQNO"])
        ldxt99_db.commit()

        time.sleep(TIME_SLEEP)

    if len(zfkhxx_list) > 0:
        wx.SendMsg("【走访消息】推送执行结束，结束时间: " + strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + "，总数：" + str(len(zfkhxx_list)), DEFAULT_QUNM)


def send_warning_notice(title, content):
    token = "5e4737eb0f9a4eab86ada8d60070409f"
    template = "html"
    topic = "longlong"
    res = requests.post(
        url="https://www.pushplus.plus/send",
        headers={

        },
        params={
            'token': token,
            'title': title,
            'content': content,
            'template': template,
            'topic': topic
        }
    )
    print(res.content.decode("utf-8"))


def do_msg_job(ldxt99_db, ldxt99_db_cursor):
    # 查询机构和群名对照表
    sql = "select * from jg_qunm_ys"
    ldxt99_db_cursor.execute(sql)
    jg_qunm_list = ldxt99_db_cursor.fetchall()

    # 查询所有待发送数据
    sql = "select * from sync_msg where flag = '0' and title is not null and msg is not null order by sync_time desc limit 400"
    ldxt99_db_cursor.execute(sql)
    msg_list = ldxt99_db_cursor.fetchall()

    # 发送数据到微信群中
    wx = WeChat()

    for msg in msg_list:
        # 查找对应的群名
        who = ""
        for jgqunm in jg_qunm_list:
            if jgqunm["jgh"] == msg["jgh"]:
                who = jgqunm["qunm"]
                break

        content = msg["title"] + "\n" + msg["msg"]

        if who == "":
            print(msg["jgh"] + "对应的群名缺失，使用默认群名")
            who = DEFAULT_QUNM

        try:
            wx.SendMsg(content, who)
        except Exception as e:
            print("发生错误：", str(e))
            print("堆栈信息如下：")
            print(traceback.format_exc())
            wx.SendMsg(content, PERSON_DEFAULT_QUNM)
            wx.SendMsg("推送信息发送异常，请检查配置的群名" + who + "是否被更新", PERSON_DEFAULT_QUNM)

        # 删除走访客户信息记录
        sql = "update sync_msg set flag = '1' where id = %s"
        ldxt99_db_cursor.execute(sql, msg["id"])
        ldxt99_db.commit()

        time.sleep(TIME_SLEEP)

    if len(msg_list) > 0:
        wx.SendMsg("【群聊消息】推送执行结束，结束时间: " + strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + "，总数：" + str(len(msg_list)), DEFAULT_QUNM)


def do_long_long_msg_job(ldxt99_db, ldxt99_db_cursor):
    # 查询机构和群名对照表
    sql = "select * from jg_qunm_ys"
    ldxt99_db_cursor.execute(sql)
    jg_qunm_list = ldxt99_db_cursor.fetchall()

    # 查询所有待发送数据
    sql = "select * from longlong_sync_msg where flag = '0' and title is not null and msg is not null order by sync_time desc limit 400"
    ldxt99_db_cursor.execute(sql)
    msg_list = ldxt99_db_cursor.fetchall()

    # 发送数据到微信群中
    wx = WeChat()

    for msg in msg_list:
        # 查找对应的群名
        who = ""
        for jgqunm in jg_qunm_list:
            if jgqunm["jgh"] == msg["jgh"]:
                who = jgqunm["qunm"]
                break

        content = msg["title"] + "\n" + msg["msg"]

        if who == "":
            print(msg["jgh"] + "对应的群名缺失，使用默认群名")
            who = DEFAULT_QUNM

        try:
            wx.SendMsg(content, who)
        except Exception as e:
            print("发生错误：", str(e))
            print("堆栈信息如下：")
            print(traceback.format_exc())
            wx.SendMsg(content, PERSON_DEFAULT_QUNM)
            wx.SendMsg("推送信息发送异常，请检查配置的群名" + who + "是否被更新", PERSON_DEFAULT_QUNM)

        # 删除走访客户信息记录
        sql = "update longlong_sync_msg set flag = '1' where id = %s"
        ldxt99_db_cursor.execute(sql, msg["id"])
        ldxt99_db.commit()

        time.sleep(TIME_SLEEP)

    if len(msg_list) > 0:
        wx.SendMsg("【群聊消息(LL)】推送执行结束，结束时间: " + strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + "，总数：" + str(len(msg_list)), DEFAULT_QUNM)


def do_person_msg_job(ldxt99_db, ldxt99_db_cursor):
    # 查询所有待发送数据
    sql = "select * from longlong_msg where STAT = '0' and TITLE is not null and CONTENT is not null and SEND_GROUP IN (SELECT SEND_GROUP FROM longlong_friends) order by IN_TIME desc limit 1000"
    ldxt99_db_cursor.execute(sql)
    long_msg_list = ldxt99_db_cursor.fetchall()

    # 拼接同一个人的所有消息再发送{"SEND_GROUP": "", "CONTENT": "", "ID": []}
    msg_list = []
    for long_msg in long_msg_list:
        send_group_is_not_in_msg_list = True
        for msg in msg_list:
            if long_msg["SEND_GROUP"] == msg["SEND_GROUP"]:
                content = msg["CONTENT"] + "\n" + "--------------------------------\n"
                content = content + long_msg["TITLE"] + "\n" + long_msg["CONTENT"]
                if len(content) > 1000:
                    continue
                else:
                    send_group_is_not_in_msg_list = False
                    msg["ID"].append(long_msg["ID"])
                    msg["CONTENT"] = content
                    break
        if send_group_is_not_in_msg_list:
            content = long_msg["TITLE"] + "\n" + long_msg["CONTENT"]
            msg_list.append({"SEND_GROUP": long_msg["SEND_GROUP"], "CONTENT": content, "ID": [long_msg["ID"]]})

    # 发送数据到微信群中
    wx = WeChat()

    for msg in msg_list:
        # 查找对应的群名
        who = msg["SEND_GROUP"]

        content = msg["CONTENT"]

        if who == "":
            print(msg["jgh"] + "对应的群名缺失，使用默认群名")
            who = DEFAULT_QUNM

        try:
            wx.SendMsg(content, who)
        except Exception as e:
            print("发生错误：", str(e))
            print("堆栈信息如下：")
            print(traceback.format_exc())
            wx.SendMsg(content, "个人信息预警群")
            wx.SendMsg("推送信息发送异常，请检查好友" + who + "是否被更新", "个人信息预警群")

        # 删除走访客户信息记录
        sql = "update longlong_msg set STAT = '1' where ID in %s"
        ldxt99_db_cursor.execute(sql, (msg["ID"],))
        ldxt99_db.commit()

        time.sleep(TIME_SLEEP)

    # 删除走访客户信息记录
    sql = "update longlong_msg set STAT = '2' where STAT = '0' AND SEND_GROUP NOT IN (SELECT SEND_GROUP FROM longlong_friends)"
    ldxt99_db_cursor.execute(sql)
    ldxt99_db.commit()

    if len(msg_list) > 0:
        wx.SendMsg("【个人消息】推送执行结束，结束时间: " + strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + "，总数：" + str(len(msg_list)), DEFAULT_QUNM)


def do_dk_job(ldxt99_db, ldxt99_db_cursor):
    # 当前日期是否为节假日
    now_date = time.strftime("%Y-%m-%d", time.localtime())
    sql = "select xtcbflag from core_bbfdxtjq where date_format(xtcadate, '%Y-%m-%d') = '" + now_date + "'"
    ldxt99_db_cursor.execute(sql)
    xtcb_date = ldxt99_db_cursor.fetchone()
    if xtcb_date["xtcbflag"] == '1':
        return

    print("开始执行上下班打卡提醒:" + strftime("%Y-%m-%d %H:%M:%S", time.localtime()))
    # 当前时间
    now_localtime = time.strftime("%H:%M:%S", time.localtime())

    if "08:10:00" <= now_localtime <= "08:30:00":
        # 发送数据到微信群中
        wx = WeChat()
        wx.SendMsg("@所有人 ⏰温馨提示：您的好友『树懒先生』已经打完卡了！人类朋友请加速移动手指~", CS_DEFAULT_QUNM)

    # 下班时间
    if "17:30:00" <= now_localtime <= "17:50:00":
        # 发送数据到微信群中
        wx = WeChat()
        wx.SendMsg("@所有人 ⏰温馨提示：「系统检测到您的下班能量已蓄满，及时完成下班打卡可解锁『准时侠』成就」",
                   CS_DEFAULT_QUNM)


def start_job():
    # 读取ftp mysql 等配置文件
    config = read_yaml(YML_PATH)

    # 连接mysql 数据源
    ldxt99_druid = config["druid"]["ldxt99"]
    # 解密数据库密码
    passwd = decrypt_data(ldxt99_druid["passwd"])
    ldxt99_db = pymysql.connect(
        host=ldxt99_druid["host"],
        port=ldxt99_druid["port"],
        user=ldxt99_druid["user"],
        passwd=passwd,
        database=ldxt99_druid["database"]
    )
    ldxt99_db_cursor = ldxt99_db.cursor(cursor=DictCursor)

    # 创建临时文件存放文件夹
    create_folder_if_not_exists(TEMP_PATH)

    try:
        # do_dk_job(ldxt99_db, ldxt99_db_cursor)

        print("开始执行龙龙机器人:" + strftime("%Y-%m-%d %H:%M:%S", time.localtime()))
        do_job(ldxt99_db, ldxt99_db_cursor)

        print("开始执行群聊消息推送:" + strftime("%Y-%m-%d %H:%M:%S", time.localtime()))
        do_msg_job(ldxt99_db, ldxt99_db_cursor)

        print("开始执行群聊消息(LL)推送:" + strftime("%Y-%m-%d %H:%M:%S", time.localtime()))
        do_long_long_msg_job(ldxt99_db, ldxt99_db_cursor)

        print("开始执行个人消息推送:" + strftime("%Y-%m-%d %H:%M:%S", time.localtime()))
        do_person_msg_job(ldxt99_db, ldxt99_db_cursor)

        do_summary_job(ldxt99_db, ldxt99_db_cursor)

    except Exception as e:
        print("发生错误：", str(e))
        print("堆栈信息如下：")
        print(traceback.format_exc())
        send_warning_notice("龙龙机器人监控", "我挂了，快来救我，sos !!!")
    finally:
        ldxt99_db.close()
        ldxt99_db_cursor.close()
        print("结束执行:" + strftime("%Y-%m-%d %H:%M:%S", time.localtime()))


def summary_content(summary_list, current_date):
    if summary_list is None or len(summary_list) == 0:
        return ""
    btjj = "    "
    content = "本日走访汇总（" + current_date + "）\n"
    content += "走访人" + btjj + "新客户" + btjj + "老客户" + btjj + "汇总\n"
    for summary in summary_list:
        dj_name = summary["DJ_OPERATORNAME"]
        if len(dj_name) == 2:
            dj_name = dj_name + "   "
        content += dj_name + "         " + str(summary["XK_COUNT"]) + "             " + str(
            summary["LK_COUNT"]) + "          " + str(summary["TODAY_COUNT"]) + "\n"
    return content


def summary_today_content(summary, current_date):
    if summary is None:
        return ""
    btjj = "    "
    content = "全行本日走访汇总（" + current_date + "）\n"
    content += "新客户" + btjj + "老客户" + btjj + "全行汇总\n"
    content += str(summary["XK_COUNT"]) + "             " + str(summary["LK_COUNT"]) + "          " + str(
        summary["TODAY_COUNT"]) + "\n"
    return content


def do_summary_job(ldxt99_db, ldxt99_db_cursor):
    current_date = strftime("%Y-%m-%d", time.localtime())

    # 判断是否有需要发送的汇总数据
    sql = "select * from sync_zfkhxx_summary where DJ_DATE = %s and FLAG = %s"
    ldxt99_db_cursor.execute(sql, (current_date, '0'))
    summary_list = ldxt99_db_cursor.fetchall()

    if summary_list is None or len(summary_list) < 1:
        return

    print("开始执行走访汇总:" + strftime("%Y-%m-%d %H:%M:%S", time.localtime()))

    sql = "select jgh, qunm from jg_qunm_ys"
    ldxt99_db_cursor.execute(sql)
    qunm_list = ldxt99_db_cursor.fetchall()

    # 发送数据到微信群中
    wx = WeChat()

    for qunm in qunm_list:
        sql = "select * from sync_zfkhxx_summary where SITENO = %s and DJ_DATE = %s and FLAG = %s"
        ldxt99_db_cursor.execute(sql, (qunm["jgh"], current_date, '0'))
        summary_list = ldxt99_db_cursor.fetchall()
        content = summary_content(summary_list, current_date)
        if len(content) == 0:
            continue

        try:
            wx.SendMsg(content, qunm["qunm"])
        except Exception as e:
            print("发生错误：", str(e))
            print("堆栈信息如下：")
            print(traceback.format_exc())
            wx.SendMsg(content, DEFAULT_QUNM)
            wx.SendMsg("推送信息发送异常，请检查配置的群名" + qunm["qunm"] + "是否被更新", DEFAULT_QUNM)
        finally:
            time.sleep(TIME_SLEEP)

    sql = "select sum(XK_COUNT) as XK_COUNT, SUM(LK_COUNT) as LK_COUNT, SUM(TODAY_COUNT) as TODAY_COUNT FROM sync_zfkhxx_summary WHERE DJ_DATE = %s and FLAG = %s "
    ldxt99_db_cursor.execute(sql, (current_date, '0'))
    today_count = ldxt99_db_cursor.fetchone()
    today_content = summary_today_content(today_count, current_date)
    if len(today_content) != 0:
        wx.SendMsg(today_content, DEFAULT_QUNM)
        time.sleep(TIME_SLEEP)

    sql = "update sync_zfkhxx_summary set FLAG = %s where DJ_DATE = %s"
    ldxt99_db_cursor.execute(sql, ('1', current_date))
    ldxt99_db.commit()

    wx.SendMsg("【汇总】今日汇总推送执行结束，结束时间: " + strftime("%Y-%m-%d %H:%M:%S", time.localtime()), DEFAULT_QUNM)


def start_summary_job():
    # 读取ftp mysql 等配置文件
    config = read_yaml(YML_PATH)

    # 连接mysql 数据源
    ldxt99_druid = config["druid"]["ldxt99"]
    # 解密数据库密码
    passwd = decrypt_data(ldxt99_druid["passwd"])
    ldxt99_db = pymysql.connect(
        host=ldxt99_druid["host"],
        port=ldxt99_druid["port"],
        user=ldxt99_druid["user"],
        passwd=passwd,
        database=ldxt99_druid["database"]
    )
    ldxt99_db_cursor = ldxt99_db.cursor(cursor=DictCursor)

    try:
        print("开始执行:" + strftime("%Y-%m-%d %H:%M:%S", time.localtime()))
        do_summary_job(ldxt99_db, ldxt99_db_cursor)
    except Exception as e:
        print("发生错误：", str(e))
        print("堆栈信息如下：")
        print(traceback.format_exc())
        send_warning_notice("龙龙机器人监控", "汇总程序挂了，快来救我，sos !!!")
    finally:
        ldxt99_db.close()
        ldxt99_db_cursor.close()
        print("结束执行:" + strftime("%Y-%m-%d %H:%M:%S", time.localtime()))


if __name__ == "__main__":
    start_job()
    schedule = BlockingScheduler()
    schedule.add_job(func=start_job, trigger='interval', minutes=TIME_DIFF, misfire_grace_time=60)
    schedule.start()
